import xlsx from 'xlsx';
import { TrafficModel } from '../models/traffic.model';
import { AppError } from '../middlewares/errorHandler';

export class ExcelService {
  private trafficModel: TrafficModel;

  constructor() {
    this.trafficModel = new TrafficModel();
  }

  // 解析Excel文件
  async parseExcel(filePath: string) {
    try {
      const workbook = xlsx.readFile(filePath);
      const sheetName = workbook.SheetNames[0];
      const worksheet = workbook.Sheets[sheetName];
      
      // 将Excel数据转换为JSON
      const data = xlsx.utils.sheet_to_json(worksheet);
      
      // 验证数据格式
      const validatedData = this.validateData(data);
      
      return validatedData;
    } catch (error) {
      throw new AppError('Excel文件解析失败', 400);
    }
  }

  // 验证数据格式
  private validateData(data: any[]) {
    const requiredFields = ['date', 'source', 'medium', 'visitors', 'page_views', 'conversions', 'bounce_rate'];
    
    const validatedData = data.map((row, index) => {
      // 检查必填字段
      for (const field of requiredFields) {
        if (!(field in row)) {
          throw new AppError(`第${index + 1}行缺少必填字段: ${field}`, 400);
        }
      }

      // 验证数值字段
      if (isNaN(Number(row.visitors)) || row.visitors < 0) {
        throw new AppError(`第${index + 1}行访问量格式错误`, 400);
      }
      if (isNaN(Number(row.page_views)) || row.page_views < 0) {
        throw new AppError(`第${index + 1}行页面浏览量格式错误`, 400);
      }
      if (isNaN(Number(row.conversions)) || row.conversions < 0) {
        throw new AppError(`第${index + 1}行转化数格式错误`, 400);
      }
      if (isNaN(Number(row.bounce_rate)) || row.bounce_rate < 0 || row.bounce_rate > 100) {
        throw new AppError(`第${index + 1}行跳出率格式错误`, 400);
      }

      // 验证日期格式
      const date = new Date(row.date);
      if (isNaN(date.getTime())) {
        throw new AppError(`第${index + 1}行日期格式错误`, 400);
      }

      // 转换数据类型
      return {
        date: date.toISOString().split('T')[0],
        source: String(row.source),
        medium: String(row.medium),
        campaign: row.campaign ? String(row.campaign) : undefined,
        visitors: Number(row.visitors),
        page_views: Number(row.page_views),
        conversions: Number(row.conversions),
        bounce_rate: Number(row.bounce_rate)
      };
    });

    return validatedData;
  }

  // 导入数据
  async importData(filePath: string) {
    const data = await this.parseExcel(filePath);
    return this.trafficModel.batchCreate(data);
  }

  // 导出数据
  async exportData(data: any[], type: 'xlsx' | 'csv' = 'xlsx') {
    // 准备表头
    const headers = [
      { header: '日期', key: 'date', width: 15 },
      { header: '来源', key: 'source', width: 15 },
      { header: '媒介', key: 'medium', width: 15 },
      { header: '活动', key: 'campaign', width: 20 },
      { header: '访问量', key: 'visitors', width: 10 },
      { header: '页面浏览量', key: 'page_views', width: 12 },
      { header: '转化数', key: 'conversions', width: 10 },
      { header: '跳出率(%)', key: 'bounce_rate', width: 10 }
    ];

    // 创建工作簿
    const workbook = xlsx.utils.book_new();
    const worksheet = xlsx.utils.json_to_sheet(data, {
      header: headers.map(h => h.key)
    });

    // 设置列宽
    worksheet['!cols'] = headers.map(h => ({ width: h.width }));

    // 添加工作表到工作簿
    xlsx.utils.book_append_sheet(workbook, worksheet, '流量数据');

    // 生成二进制数据
    const buffer = type === 'xlsx' 
      ? xlsx.write(workbook, { type: 'buffer', bookType: 'xlsx' })
      : xlsx.write(workbook, { type: 'buffer', bookType: 'csv' });

    return {
      buffer,
      filename: `traffic_data_${new Date().toISOString().split('T')[0]}.${type}`
    };
  }

  // 生成PDF报表
  async generateReport(data: any) {
    // TODO: 实现PDF报表生成
    throw new AppError('功能开发中', 501);
  }
} 